package com.junzhiit.nengji.shop.dao.impl;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.apache.commons.lang3.StringUtils;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Propagation;
import org.springframework.transaction.annotation.Transactional;

import com.bainaeco.framework.base.dao.AbstractBaseDAO;
import com.bainaeco.framework.common.jdbc.StatementParameter;
import com.bainaeco.framework.enums.IsDelete;
import com.junzhiit.nengji.shop.bo.GoodsBO;
import com.junzhiit.nengji.shop.bo.OrdersGoodsInfoBO;
import com.junzhiit.nengji.shop.dao.GoodsDAO;
import com.junzhiit.nengji.shop.model.Goods;
import com.junzhiit.nengji.shop.model.enums.AreaLevel;
import com.junzhiit.nengji.shop.model.enums.GoodsImagesType;
import com.junzhiit.nengji.shop.model.enums.GoodsSearchStatus;
import com.junzhiit.nengji.shop.model.enums.GoodsStatus;
import com.junzhiit.nengji.shop.model.enums.Is;
import com.junzhiit.nengji.shop.model.enums.ShopIsPlatform;
import com.junzhiit.nengji.shop.model.enums.ShopStatus;
import com.junzhiit.nengji.shop.model.enums.TypeLevel;

@Repository
public class GoodsDAOImpl extends AbstractBaseDAO<Goods> implements GoodsDAO {

	@Override
	public List<GoodsBO> findPageAll(GoodsBO goodsBO, int pageNumber, int maxPageRow,Integer isPlatform) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append(" select ");
		sql.append(" g.`audit_fail_description`,g.food_box_charge,g.`code`,g.updated_time,g.id,g.`name`,g.price,g.reference_price,g.is_help_peasant,g.sales_volume ");
		sql.append(",g.is_open_sku,g.is_open_member,g.is_free_shipping,g.`status`,g.is_recommend,g.is_open_rebate");
		sql.append(",g.profit,g.postage_type,g.postage_price,g.shop_id,g.is_open_sell,g.discount,g.type,");
		//postage_template name
		sql.append(" (select name from postage_template pt where pt.id=g.postage_template_id) postage_template_name,");
		
		//stock by sku
		sql.append(" (case g.is_open_sku ");
		sql.append(" when 0 then g.stock ");
		sql.append(" when 1 then (select sum(sc.stock) from sku_content sc where sc.goods_id=g.id)");
		sql.append(" end)");
		sql.append(" stock,");
		
		//price by sku
		sql.append(" (case g.is_open_sku ");
		sql.append(" when 1 then (select case min(sc.price)=max(sc.price) when 0 then concat_ws('~',min(sc.price),max(sc.price)) when 1 then min(sc.price)  end  from sku_content sc where sc.goods_id=g.id) ");
		sql.append(" end)");
		sql.append(" sku_range_price,");
		
		
		sql.append(" b.`name` brand_name, ");
		sql.append(" t.`name` type_name, ");
		sql.append(" s.`name` shop_name,s.is_platform isPlatform, ");
		sql.append(" gi.file_name,");
		
		//systemType concat
		sql.append(" (select group_concat(st.`name`)  from goods_system_type gst inner join system_type st on gst.system_type_id=st.id  where gst.goods_id=g.id) systemType");
		sql.append("  from goods g ");
		
		if(goodsBO.getCheckType() != null && goodsBO.getCheckType()==Is.NO.toIntValue()){
			sql.append(" inner join shop s on g.shop_id=s.id and s.is_chain = ?");
			param.setInt(goodsBO.getCheckType());
		}else if(goodsBO.getCheckType() != null && goodsBO.getCheckType()==Is.YES.toIntValue()){
			sql.append(" inner join shop s on g.shop_id=s.id and s.is_chain = ? and s.chain_id = ?");
			param.setInt(goodsBO.getCheckType());
			param.setString(goodsBO.getChainId());
		}else{
			sql.append(" inner join shop s on g.shop_id=s.id ");
		}
		sql.append(" left join brand b on g.brand_id=b.id ");
		sql.append(" left join type t on g.type_id=t.id ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=? ");
		sql.append(" where g.is_delete=? ");

		
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		if (isPlatform != null) {
			sql.append(" and s.is_platform = ? ");
			param.setInt(isPlatform);
		}
		if(goodsBO.getIsChain()!=null){
			sql.append(" and s.is_chain = ? ");
			param.setInt(goodsBO.getIsChain());
		}

		if (StringUtils.isNotBlank(goodsBO.getShopId())) {
			sql.append(" and g.shop_id=? ");
			param.setString(goodsBO.getShopId());
		}

		if (StringUtils.isNotBlank(goodsBO.getName())) {
			sql.append(" and g.name like ? ");
			param.setString("%" + goodsBO.getName() + "%");
		}

		if (StringUtils.isNotBlank(goodsBO.getBrandId())) {
			sql.append(" and g.brand_id=? ");
			param.setString(goodsBO.getBrandId());
		}

		if (StringUtils.isNotBlank(goodsBO.getTypeId())) {
			sql.append(" and g.type_id=? ");
			param.setString(goodsBO.getTypeId());
		}

		if (goodsBO.getStatus() != null) {
			sql.append(" and g.status=? ");
			param.setInt(goodsBO.getStatus());
		}
		if(goodsBO.getType() != null){
			sql.append(" and g.type = ?");
			param.setInt(goodsBO.getType());
		}
		
		sql.append(" order by g.created_time desc");

		return super.queryForPagingList(sql.toString(), GoodsBO.class, param, pageNumber,maxPageRow);
	}

	@Override
	public int getTotalCount(GoodsBO goodsBO,Integer isPlatform) {
		StringBuffer sql = new StringBuffer();
		StatementParameter param = new StatementParameter();
		sql.append(" select count(g.id) from goods g ");
		if(goodsBO.getCheckType() != null && goodsBO.getCheckType()==Is.NO.toIntValue()){
			sql.append(" inner join shop s on g.shop_id=s.id and s.is_chain = ?");
			param.setInt(goodsBO.getCheckType());
		}else if(goodsBO.getCheckType() != null && goodsBO.getCheckType()==Is.YES.toIntValue()){
			sql.append(" inner join shop s on g.shop_id=s.id and s.is_chain = ? and s.chain_id = ?");
			param.setInt(goodsBO.getCheckType());
			param.setString(goodsBO.getChainId());
		}else{
			sql.append(" inner join shop s on g.shop_id=s.id ");
		}
		sql.append(" left join brand b on g.brand_id=b.id ");
		sql.append(" left join type t on g.type_id=t.id and t.`level`=? ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=? ");
		sql.append(" where g.is_delete=? ");

		
		param.setInt(TypeLevel.THREE.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		if (isPlatform != null) {
			sql.append(" and s.is_platform = ?  ");
			param.setInt(isPlatform);
		}
		if(goodsBO.getIsChain()!=null){
			sql.append(" and s.is_chain = ? ");
			param.setInt(goodsBO.getIsChain());
		}
		if (StringUtils.isNotBlank(goodsBO.getShopId())) {
			sql.append(" and g.shop_id=? ");
			param.setString(goodsBO.getShopId());
		}

		if (StringUtils.isNotEmpty(goodsBO.getName())) {
			sql.append(" and g.name like ? ");
			param.setString("%" + goodsBO.getName() + "%");
		}

		if (StringUtils.isNotEmpty(goodsBO.getBrandId())) {
			sql.append(" and g.brand_id=? ");
			param.setString(goodsBO.getBrandId());
		}

		if (StringUtils.isNotEmpty(goodsBO.getTypeId())) {
			sql.append(" and g.type_id=? ");
			param.setString(goodsBO.getTypeId());
		}

		if (goodsBO.getStatus() != null) {
			sql.append(" and g.status=? ");
			param.setInt(goodsBO.getStatus());
		}
		if(goodsBO.getType() != null){
			sql.append(" and g.type = ?");
			param.setInt(goodsBO.getType());
		}
		sql.append(" order by g.created_time desc");
		return super.queryForInt(sql.toString(), param);
	}

	@Override
	public GoodsBO findByIdForOther(String id) {

		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" g.`code`,g.hint,g.food_box_charge,g.id,g.`name`,g.price,g.updated_time,g.reference_price,g.is_open_sku,g.is_open_member,g.is_free_shipping,g.`status`,g.is_recommend,g.is_open_rebate,g.is_open_sell,g.discount,g.type,");
		sql.append(" g.is_help_peasant isHelpPeasant ");
		sql.append(" ,g.is_open_member,g.is_free_shipping,g.`status`,g.is_recommend,g.is_open_rebate");
		sql.append(" ,g.is_open_sell,g.discount, ");
		//stock by sku
		sql.append(" (case g.is_open_sku ");
		sql.append(" when 0 then g.stock ");
		sql.append(" when 1 then (select sum(sc.stock) from sku_content sc where sc.goods_id=g.id)");
		sql.append(" end)");
		sql.append(" stock,");
		
		//price by sku
		sql.append(" (case g.is_open_sku ");
		sql.append(" when 1 then (select case min(sc.price)=max(sc.price) when 0 then concat_ws('~',min(sc.price),max(sc.price)) when 1 then min(sc.price)  end  from sku_content sc where sc.goods_id=g.id) ");
		sql.append(" end)");
		sql.append(" sku_range_price,");
		
		sql.append(" b.`name` brand_name, ");
		sql.append(" t.`name` type_name, ");
		sql.append(" s.`name` shop_name,s.id shopId,s.shop_head_img_url shopHeadImgUrl,s.is_platform isPlatform, ");
		sql.append(" gi.file_name ");
		sql.append("  from goods g ");
		sql.append(" left join brand b on g.brand_id=b.id ");
		sql.append(" left join type t on g.type_id=t.id and t.`level`=? ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=? ");
		sql.append(" left join shop s on g.shop_id=s.id ");
		sql.append(" where g.id=?");

		StatementParameter param = new StatementParameter();
		param.setInt(TypeLevel.THREE.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setString(id);

		return super.query(sql.toString(), GoodsBO.class, param);
	}

	@Override
	public String queryMaxCode() {
		String sql = "select ifnull(max(convert(code,signed)),0)  from goods";
		StatementParameter param = new StatementParameter();
		return super.queryForString(sql, param);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	@Override
	public boolean batchUpdateStatus(String[] ids, Integer status) {
		final StringBuffer sql = new StringBuffer();
		final StatementParameter param = new StatementParameter();
		sql.append("update goods set status=? where 1=1 and");

		if (ids.length > 0) {

			sql.append(" id in (");
			param.setInt(status);
			for (int i = 0, len = ids.length; i < ids.length; i++) {
				sql.append("?");
				if (i < len - 1) {
					sql.append(",");
				}
				param.setString(ids[i]);
			}
			sql.append(")");

		}

		return super.updateForBoolean(sql.toString(), param);
	}

	@Transactional(readOnly = false, propagation = Propagation.REQUIRED)
	@Override
	public boolean updateStatus(String id, Integer status) {
		String sql = "update goods set status=?,updated_time=? where id=?";

		StatementParameter param = new StatementParameter();
		param.setInt(status);
		param.setDate(new Date());
		param.setString(id);

		return super.updateForBoolean(sql, param);
	}

	@Override
	public List<GoodsBO> findAll(String shopId, Integer status,Integer goodsSearchType,int pageNumber, int maxPageRow) {
		StringBuffer sql = new StringBuffer();
		sql.append("select (case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id = g.id)end) price,");
		sql.append("g.`code`,g.hint,g.id,g.`name`,g.reference_price,g.stock,g.is_open_sku,g.sales_volume,");
		sql.append("g.is_open_member,g.food_box_charge,g.is_free_shipping,g.`status`,g.is_recommend,g.is_open_rebate,g.is_open_sell,g.is_help_peasant isHelpPeasant,g.discount,");
		sql.append(" b.`name` brand_name,");
		sql.append(" t.`name` type_name,");
		sql.append(" s.`name` shop_name,s.is_platform isPlatform, ");
		sql.append(" gi.file_name,");
		
		//systemType concat
		sql.append(" (select group_concat(st.`name`)  from goods_system_type gst inner join system_type st on gst.system_type_id=st.id  where gst.goods_id=g.id) systemType");
		sql.append(" from goods g");
		sql.append(" inner join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=? and gi.is_delete=?");
		sql.append(" where g.is_delete=? and g.status =?  and (s.is_platform=? or ( s.is_platform=? and g.is_synchronous = ?) ) ");

		StatementParameter param = new StatementParameter();
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		
		param.setInt(ShopIsPlatform.HEADQUARTERS.toIntStatus());
		param.setInt(ShopIsPlatform.OWN.toIntStatus());
		param.setInt(Is.YES.toIntValue());
		
		if (StringUtils.isNotBlank(shopId)) {
			sql.append(" and g.shop_id=? ");
			param.setString(shopId);
		}
		
		if(goodsSearchType!=null){
			sql.append(" and g.type =?");
			param.setInt(goodsSearchType);
		}
		
		if(status!=null){
			if(status==GoodsSearchStatus.ALL.toIntStatus()){
				sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
			}else if(status==GoodsSearchStatus.RECOMMEND.toIntStatus()){
				sql.append(" and g.is_recommend=? order by g.is_open_rebate desc,g.sales_volume desc");
				param.setInt(Is.YES.toIntValue());
			}else if(status==GoodsSearchStatus.PRICEDESC.toIntStatus()){
				sql.append(" order by price desc,g.is_open_rebate desc,g.sales_volume desc");
			}else if(status==GoodsSearchStatus.PRICEASC.toIntStatus()){
				sql.append(" order by price asc,g.is_open_rebate desc,g.sales_volume desc");
			}else if(status==GoodsSearchStatus.DISCOUNT.toIntStatus()){
				sql.append(" and g.discount!=0 order by g.discount asc,g.is_open_rebate desc,g.sales_volume desc");
			}else if(status==GoodsSearchStatus.SALES_VOLUME.toIntStatus()){
				sql.append(" order by g.sales_volume desc ");
			}
		}else{
			sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
		}

		return super.queryForPagingList(sql.toString(), GoodsBO.class, param, pageNumber, maxPageRow);
	}

	@Override
	public int findAllByPageTotalCount(String shopId, Integer status,Integer goodsSearchType) {
		StringBuffer sql = new StringBuffer();
		sql.append("select count(g.id) from goods g");
		sql.append(" inner join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id");
		sql.append(" left join (select syt.name,gst.goods_id from system_type syt  left join goods_system_type gst on syt.id = gst.system_type_id )st on g.id = st.goods_id ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=? and gi.is_delete=?");
		sql.append(" where g.is_delete=? and g.status =?  and (s.is_platform=? or ( s.is_platform=? and g.is_synchronous = ?) ) ");

		StatementParameter param = new StatementParameter();
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		
		param.setInt(ShopIsPlatform.HEADQUARTERS.toIntStatus());
		param.setInt(ShopIsPlatform.OWN.toIntStatus());
		param.setInt(Is.YES.toIntValue());
		
		if (StringUtils.isNotBlank(shopId)) {
			sql.append(" and g.shop_id=? ");
			param.setString(shopId);
		}
		
		if(goodsSearchType!=null){
			sql.append(" and g.type =?");
			param.setInt(goodsSearchType);
		}
		
		if(status!=null){
			if(status==GoodsSearchStatus.ALL.toIntStatus()){
				sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
			}else if(status==GoodsSearchStatus.RECOMMEND.toIntStatus()){
				sql.append(" and g.is_recommend=? order by g.is_open_rebate desc,g.sales_volume desc");
				param.setInt(Is.YES.toIntValue());
			}else if(status==GoodsSearchStatus.PRICEDESC.toIntStatus()){
				sql.append(" order by price desc,g.is_open_rebate desc,g.sales_volume desc");
			}else if(status==GoodsSearchStatus.PRICEASC.toIntStatus()){
				sql.append(" order by price asc,g.is_open_rebate desc,g.sales_volume desc");
			}else if(status==GoodsSearchStatus.DISCOUNT.toIntStatus()){
				sql.append(" and g.discount!=0 order by g.discount asc,g.is_open_rebate desc,g.sales_volume desc");
			}
		}else{
			sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
		}
		return super.queryForInt(sql.toString(),param);
	}
	
	@Override
	public GoodsBO getGoodsByCode(String code) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" g.`code`,g.shop_id,g.food_box_charge,g.type,g.hint,g.id,g.`name`,g.price,g.reference_price,g.stock,g.is_open_sku,ar.short_name 'city',");
		sql.append(" g.is_open_member,g.is_free_shipping,g.is_help_peasant isHelpPeasant,g.`status`,g.is_recommend,g.is_open_rebate,s.mobile_phone,g.sales_volume,");
		sql.append(" b.`name` brand_name,");
		sql.append(" t.`name` type_name,g.type_id typeId,");
		sql.append(" s.status shopStatus,");
		sql.append(" gi.file_name,g.postage_type,g.postage_price,g.postage_template_id,g.postage_weight,");
		sql.append(" s.`name` shop_name,s.is_platform isPlatform,s.shop_head_img_url,s.address,s.lat,s.lng,g.is_open_sell,g.discount");
		sql.append(" from goods g");
		sql.append(" left join brand b on g.brand_id=b.id");
		sql.append(" left join type t on g.type_id=t.id and t.`level`=?");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=?");
		sql.append(" left join shop s on g.shop_id=s.id");
		sql.append(" left join area ar on s.area_city_id=ar.id and ar.level=?");
		sql.append(" where g.code=?");

		StatementParameter param = new StatementParameter();
		param.setInt(TypeLevel.THREE.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(AreaLevel.ONE.toIntValue());
		param.setString(code);

		return super.query(sql.toString(), GoodsBO.class, param);
	}

	@Override
	public List<String> getFileNameByCode(String code, GoodsImagesType type) {
		StringBuffer sql = new StringBuffer();
		sql.append(" select ");
		sql.append(" gi.file_name ");
		sql.append(" from goods g ");
		sql.append(" left join type t on g.type_id=t.id and t.`level`=? ");
		sql.append(" left join goods_images gi on g.id=gi.goods_id and gi.type=? and gi.is_delete=?");
		sql.append(" left join shop s on g.shop_id=s.id ");
		sql.append(" where g.code=? order by gi.sort asc");

		StatementParameter param = new StatementParameter();
		param.setInt(TypeLevel.THREE.toIntValue());
		param.setInt(type.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(code);

		List<GoodsBO> listBO = super.queryForList(sql.toString(), GoodsBO.class, param);

		List<String> list = new ArrayList<String>();
		for (GoodsBO goodsBO : listBO) {
			list.add(goodsBO.getFileName());
		}

		return list;
	}

	@Override
	public int checkGoods(String id) {
		StatementParameter params = new StatementParameter();
		StringBuilder sql = new StringBuilder();
		sql.append("select count(id) from goods where id = ? and status = ? and is_delete = ?");
		params.setString(id);
		params.setInt(GoodsStatus.ACTIVE.toIntStatus());
		params.setInt(IsDelete.NO.toIntValue());
		return super.queryForInt(sql.toString(), params);
	}
	
	@Override
	public GoodsBO getGoodsByBarcode(String userId,String shopId,String goodsId,String skuContentId) {
		StringBuilder sql = new StringBuilder();
		sql.append("select g.id,i.file_name as cover_image,g.food_box_charge,g.`code`,g.name as name,ifnull(ssc.stock,g.stock) as stock");
		sql.append(",g.is_free_shipping,g.is_open_sku,g.is_help_peasant isHelpPeasant,ssc.standards,ssc.price as original_price,ssc.price");
		sql.append(",g.shop_id,sp.name as shop_name,sp.is_platform isPlatform,sp.shop_head_img_url,sp.type as shop_type");
		sql.append(",g.type_id,ssc.id as skuContentId,g.reference_price");
		sql.append(",g.postage_type,g.postage_price,g.postage_template_id,g.postage_weight,g.is_open_sell,g.discount,g.is_open_rebate ");
		sql.append(" from sku_content ssc");
		sql.append(" inner join goods g on g.id = ssc.goods_id and g.is_open_sku =?");
		sql.append(" inner join type t on t.id = g.type_id");
		sql.append(" inner join goods_images i on i.goods_id = g.id and i.type = ? and i.is_delete = ?");
		sql.append(" inner join shop sp on g.shop_id = sp.id");
		
		sql.append(" left join user u on u.id  = ?");
		sql.append(" left join user_config uc on uc.user_id  = u.id");
		
		sql.append(" where ssc.goods_id = ? and ssc.id = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.YES.toIntValue());
		params.setInt(GoodsImagesType.COVER.toIntStatus());
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(userId);
		params.setString(goodsId);
		params.setString(skuContentId);
		return query(sql.toString(), GoodsBO.class, params);
	}
	
	@Override
	public GoodsBO getGoodsInfo(String userId,String shopId,String goodsId) {
		StringBuilder sql = new StringBuilder();
		sql.append(" select g.*,sp.name as shop_name,sp.is_platform isPlatform,i.file_name as cover_image");
		sql.append(" ,sp.type as shop_type,sp.shop_head_img_url ");
		sql.append(" from goods g");
		sql.append(" inner join type t on t.id = g.type_id");
		sql.append(" inner join goods_images i on i.goods_id = g.id and i.type = ? and i.is_delete = ?");
		sql.append(" inner join shop sp on g.shop_id = sp.id");
		sql.append(" left join user u on u.id  = ?");
		sql.append(" left join user_config uc on uc.user_id  = u.id");
		sql.append(" where g.is_open_sku = ? and g.shop_id = ? and g.id =?");
		StatementParameter params = new StatementParameter();
		params.setInt(GoodsImagesType.COVER.toIntStatus());
		params.setInt(IsDelete.NO.toIntValue());
		params.setString(userId);
		params.setInt(Is.NO.toIntValue());
		params.setString(shopId);
		params.setString(goodsId);
		return query(sql.toString(), GoodsBO.class, params);
	}

	 public OrdersGoodsInfoBO findGoodsIdIsSku(String userId,String shopId,String skuContentId){
			StatementParameter params = new StatementParameter();
			StringBuilder sql = new StringBuilder();
			sql.append(" select g.id as goodsId,ssc.id as skuContentId,ssc.standards as skuContentName");
			sql.append(" ,ssc.price as price,g.name as goodsName,g.reference_price as referencePrice,g.type ");
			sql.append(" ,g.is_open_sku as isOpenSku,g.is_open_member as isOpenMember,i.file_name as fileName,g.type_id as typeId");
			sql.append(" ,g.code as goodsCode,g.profit,g.is_open_sell,g.is_open_rebate ");
			sql.append(" from goods g");
			sql.append(" inner join sku_content ssc on ssc.goods_id = g.id");
			sql.append(" left join goods_images i on i.goods_id = g.id and i.type = ? and i.is_delete = ?");
			sql.append(" left join user u on u.id  = ?");
			sql.append(" left join user_config uc on uc.user_id  = u.id");
			sql.append(" where ssc.is_delete = ? and ssc.id = ? ");
			sql.append(" and g.is_open_sku = ? and g.is_delete = ? and g.shop_id = ?");
			params.setInt(GoodsImagesType.COVER.toIntStatus());
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(userId);
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(skuContentId);
			params.setInt(Is.YES.toIntValue());
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(shopId);
			return super.query(sql.toString(),OrdersGoodsInfoBO.class, params);
	}
	 
	 public OrdersGoodsInfoBO findGoodsId(String userId,String shopId,String goodsId) {
			StatementParameter params = new StatementParameter();
			StringBuilder sql = new StringBuilder();
			sql.append(" select g.id as goodsId,g.name as goodsName,g.reference_price as referencePrice,g.hint as hint");
			sql.append(" ,g.type,g.is_open_sku as isOpenSku,g.is_open_member as isOpenMember,g.type_id as typeId");
			sql.append(" ,i.file_name as fileName,g.price as price,g.code as goodsCode,g.profit,g.is_open_sell,g.is_open_rebate");
			sql.append(" from goods g");
			sql.append(" left join goods_images i on i.goods_id = g.id and i.type = ? and i.is_delete = ?");
			sql.append(" left join user u on u.id  = ?");
			sql.append(" left join user_config uc on uc.user_id  = u.id");
			sql.append(" where g.is_delete = ? and g.id =?");
			sql.append(" and g.is_open_sku = ? and g.shop_id = ?");
			params.setInt(GoodsImagesType.COVER.toIntStatus());
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(userId);
			params.setInt(IsDelete.NO.toIntValue());
			params.setString(goodsId);
			params.setInt(Is.NO.toIntValue());
			params.setString(shopId);
			return super.query(sql.toString(),OrdersGoodsInfoBO.class, params);
		}

	@Override
	public int getCountByGoodsApproval() {
		String sql = "select count(id) from goods where status = ? and is_delete = ?";
		StatementParameter params = new StatementParameter();
		params.setInt(GoodsStatus.AUDIT.toIntStatus());
		params.setInt(Is.NO.toIntValue());
		return super.queryForInt(sql.toString(), params);
	}

	@Override
	public List<GoodsBO> findGoods(String goodsName) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		
		sql.append(" select g.id,g.name,g.food_box_charge,g.code,g.reference_price,g.stock,gi.file_name,g.is_open_sku,g.sales_volume,g.shop_id,g.hint,");
		sql.append(" g.is_open_member,g.is_free_shipping,g.`status`,g.is_recommend,g.is_open_rebate,s.`name` shop_name,s.is_platform isPlatform,g.is_open_sell,g.is_help_peasant isHelpPeasant,g.discount,");
		sql.append(" (case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id = g.id)end) price");
		sql.append(" from goods g left join goods_images gi on g.id=gi.goods_id and gi.type=? and gi.is_delete=?");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" where g.is_delete=? and g.status =? and g.name like ?");
		sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
		
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setString("%"+goodsName+"%");
		
		return queryForList(sql.toString(), GoodsBO.class, param);
	}
	
	@Override
	public List<GoodsBO> findGoods(String goodsName,String typeId,String shopId,Integer status) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		
		sql.append(" select g.id,g.name,g.food_box_charge,g.code,g.reference_price,g.stock,gi.file_name,g.is_open_sku,g.sales_volume,g.shop_id,g.hint,");
		sql.append(" g.is_open_member,g.is_free_shipping,g.is_help_peasant isHelpPeasant,g.`status`,g.is_recommend,g.is_open_rebate,s.`name` shop_name,s.is_platform isPlatform,g.is_open_sell,g.discount,  ");
		sql.append(" (case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id = g.id)end) price");
		sql.append(" from goods g left join goods_images gi on g.id=gi.goods_id and gi.type=? and gi.is_delete=?");
		sql.append(" left join type t on g.type_id = t.id ");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" where g.is_delete=? and g.status =? and g.name like ? ");
		
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setString("%"+goodsName+"%");
		
		if(StringUtils.isNoneBlank(typeId)){
			sql.append(" and t.id = ? ");
			param.setString(typeId);
		}
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and s.id = ? ");
			param.setString(shopId);
		}
		
		if(status!=null){
			if(status.equals(GoodsSearchStatus.SALES_VOLUME.toIntStatus())){
				sql.append(" order by g.sales_volume desc");
			}else if(status.equals(GoodsSearchStatus.NEW_TIME.toIntStatus())){
				sql.append(" order by g.created_time desc");
			}else{
				sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
			}
		}else{
			sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
		}
		
		return queryForList(sql.toString(), GoodsBO.class, param);
	}
	
	@Override
	public List<GoodsBO> findGoodsBySystemType(String goodsName,String systemTypeId,String shopId,Integer status) {
		StringBuffer sql=new StringBuffer();
		StatementParameter param=new StatementParameter();
		
		sql.append(" select g.id,g.name,g.food_box_charge,g.code,g.reference_price,g.stock,gi.file_name,g.is_open_sku,g.is_help_peasant isHelpPeasant,g.sales_volume,g.shop_id,g.hint,");
		sql.append(" g.is_open_member,g.is_free_shipping,g.`status`,g.is_recommend,g.is_open_rebate,s.`name` shop_name,s.is_platform isPlatform,g.is_open_sell,g.discount,  ");
		sql.append(" (case g.is_open_sku when ? then g.price when ? then (select min(sc.price)");
		sql.append(" from sku_content sc where sc.is_delete = ? and sc.goods_id = g.id)end) price ");
		sql.append(" from goods g left join goods_images gi on g.id=gi.goods_id and gi.type=? and gi.is_delete=?");
		sql.append(" left join type t on g.type_id = t.id ");
		sql.append(" left join shop s on g.shop_id=s.id and s.status<>?");
		sql.append(" inner join (select syt.`name`,gst.goods_id,syt.id from goods_system_type gst left join system_type syt on gst.system_type_id = syt.id)st on g.id = st.goods_id ");
		sql.append(" where g.is_delete=? and g.status =? and (s.is_platform=? or g.is_synchronous = ? ) and g.name like ? ");
		
		param.setInt(Is.NO.toIntValue());
		param.setInt(Is.YES.toIntValue());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsImagesType.COVER.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(ShopStatus.STOP.toIntStatus());
		param.setInt(IsDelete.NO.toIntValue());
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		param.setInt(ShopIsPlatform.HEADQUARTERS.toIntStatus());
		param.setInt(Is.YES.toIntValue());
		param.setString("%"+goodsName+"%");
		
		if(StringUtils.isNoneBlank(systemTypeId)){
			sql.append(" and st.id = ? ");
			param.setString(systemTypeId);
		}
		
		if(StringUtils.isNotBlank(shopId)){
			sql.append(" and s.id = ? ");
			param.setString(shopId);
		}
		
		if(status!=null){
			if(status.equals(GoodsSearchStatus.SALES_VOLUME.toIntStatus())){
				sql.append(" order by g.sales_volume desc");
			}else if(status.equals(GoodsSearchStatus.NEW_TIME.toIntStatus())){
				sql.append(" order by g.created_time desc");
			}else{
				sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
			}
		}else{
			sql.append(" order by g.is_open_rebate desc,g.sales_volume desc");
		}
		
		return queryForList(sql.toString(), GoodsBO.class, param);
	}

	@Override
	public List<Goods> findAllGoods(String shopId) {
		StringBuffer sql=new StringBuffer();
		sql.append("select * from goods where is_delete = ? and shop_id = ? and status = ?");
		StatementParameter param=new StatementParameter();
		param.setInt(IsDelete.NO.toIntValue());
		param.setString(shopId);
		param.setInt(GoodsStatus.ACTIVE.toIntStatus());
		return queryForList(sql.toString(), Goods.class, param);
	}

	@Override
	public boolean updateIsOpenSell() {
		StringBuffer sql = new StringBuffer();
		sql.append("update goods set is_open_sell =? where is_open_sell =?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		return super.updateForBoolean(sql.toString(),params);
	}

	@Override
	public boolean updateIsOpenRebate() {
		StringBuffer sql = new StringBuffer();
		sql.append("update goods set is_open_rebate =? where is_open_rebate =?");
		StatementParameter params = new StatementParameter();
		params.setInt(Is.NO.toIntValue());
		params.setInt(Is.YES.toIntValue());
		return super.updateForBoolean(sql.toString(),params);
	}

	@Override
	public boolean updateIsOpenSellByShopId(Integer isOpenSell,String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("update goods set is_open_sell =? where shop_id = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(isOpenSell);
		params.setString(shopId);
		return super.updateForBoolean(sql.toString(), params);
	}

	@Override
	public boolean updateIsOpenRebateByShopId(Integer IsOpenRebate,String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("update goods set is_open_rebate =? where shop_id = ?");
		StatementParameter params = new StatementParameter();
		params.setInt(IsOpenRebate);
		params.setString(shopId);
		return super.updateForBoolean(sql.toString(), params);
	}

	@Override
	public List<GoodsBO> getGoodsByShopId(String shopId) {
		StringBuffer sql = new StringBuffer();
		sql.append("select gi.file_name,g.id,g.code from goods g");
		sql.append(" join goods_images gi on gi.goods_id = g.id and gi.type = 0");
		sql.append(" where g.shop_id = ? ORDER BY g.sales_volume desc");
		sql.append(" LIMIT 0,3");
		StatementParameter params = new StatementParameter();
		params.setString(shopId);
		return super.queryForList(sql.toString(),GoodsBO.class,params);
	}

}
